﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace Helpers.Database
{
    /// <summary>
    /// Contains a set of helpers methods for database interaction.
    /// </summary>
    public static class HelperDatabase
    {
        #region Static Methods

        public static DataSet ExecuteProcedure(string procedure, string connectionString)
        {
            return HelperDatabase.ExecuteProcedure(procedure, new List<SqlParameter>(), connectionString);
        }
        public static DataSet ExecuteProcedure(string procedure, List<SqlParameter> parameters, string connectionString)
        {
            DataSet result = null;

            SqlConnection dbCon = new SqlConnection(connectionString);

            result = HelperDatabase.ExecuteProcedure(procedure, parameters, dbCon);

            return result;
        }
        public static DataSet ExecuteProcedure(string procedure, SqlConnection dbConnection)
        {
            return HelperDatabase.ExecuteProcedure(procedure, new List<SqlParameter>(), dbConnection);
        }
        public static DataSet ExecuteProcedure(string procedure, List<SqlParameter> parameters, SqlConnection dbConnection)
        {
            DataSet dataset = new DataSet();

            /* ------------------------- */
            /* ---- Open connection ---- */
            /* ------------------------- */
            if (dbConnection.State != ConnectionState.Open)
                dbConnection.Open();


            SqlCommand comm = new SqlCommand();
            comm.Connection = dbConnection;
            comm.CommandText = procedure;
            comm.CommandType = CommandType.StoredProcedure;

            foreach (SqlParameter param in parameters)
                comm.Parameters.Add(param);

            SqlDataAdapter adapter = new SqlDataAdapter(comm);

            adapter.Fill(dataset);

            adapter.Dispose();
            comm.Dispose();

            return dataset;
        }
        public static int ExecuteNonProcedure(string query, string connectionString)
        {
            return HelperDatabase.ExecuteNonQuery(query, connectionString, CommandType.StoredProcedure);
        }
        public static int ExecuteNonProcedure(string query, List<SqlParameter> parameters, string connectionString)
        {
            return HelperDatabase.ExecuteNonQuery(query, parameters, connectionString, CommandType.StoredProcedure);
        }
        public static int ExecuteNonProcedure(string query, List<SqlParameter> parameters, SqlConnection dbConnection)
        {
            return HelperDatabase.ExecuteNonQuery(query, parameters, dbConnection, CommandType.StoredProcedure);
        }
        public static int ExecuteNonProcedure(string query, SqlConnection dbConnection)
        {
            return HelperDatabase.ExecuteNonQuery(query, dbConnection, CommandType.StoredProcedure);
        }
        public static int ExecuteNonQuery(string query, string connectionString)
        {
            return HelperDatabase.ExecuteNonQuery(query, connectionString, CommandType.Text);
        }
        public static int ExecuteNonQuery(string query, string connectionString, CommandType cmdType)
        {
            return HelperDatabase.ExecuteNonQuery(query, new List<SqlParameter>(), connectionString, cmdType);
        }
        public static int ExecuteNonQuery(string query, List<SqlParameter> parameters, string connectionString)
        {
            return HelperDatabase.ExecuteNonQuery(query, parameters, connectionString, CommandType.Text);
        }
        public static int ExecuteNonQuery(string query, List<SqlParameter> parameters, string connectionString, CommandType cmdType)
        {
            int result;

            SqlConnection dbCon = new SqlConnection(connectionString);

            result = HelperDatabase.ExecuteNonQuery(query, parameters, dbCon, cmdType);

            dbCon.Dispose();

            return result;
        }
        public static int ExecuteNonQuery(string query, SqlConnection dbConnection)
        {
            return HelperDatabase.ExecuteNonQuery(query, dbConnection, CommandType.Text);
        }
        public static int ExecuteNonQuery(string query, SqlConnection dbConnection, CommandType cmdType)
        {
            return HelperDatabase.ExecuteNonQuery(query, new List<SqlParameter>(), dbConnection, cmdType);
        }
        public static int ExecuteNonQuery(string query, List<SqlParameter> parameters, SqlConnection dbConnection)
        {
            int result;

            result = HelperDatabase.ExecuteNonQuery(query, parameters, dbConnection, CommandType.Text);

            return result;
        }
        public static int ExecuteNonQuery(string query, List<SqlParameter> parameters, SqlConnection dbConnection, CommandType cmdType)
        {
            /* ------------------------- */
            /* ---- Open connection ---- */
            /* ------------------------- */
            if (dbConnection.State != ConnectionState.Open)
                dbConnection.Open();


            SqlCommand comm = new SqlCommand();
            comm.Connection = dbConnection;
            comm.CommandText = query;
            comm.CommandType = cmdType;

            foreach (SqlParameter param in parameters)
                comm.Parameters.Add(param);

            SqlParameter returnParam = new SqlParameter();
            returnParam.Direction = ParameterDirection.ReturnValue;
            comm.Parameters.Add(returnParam);

            comm.ExecuteNonQuery();

            comm.Dispose();

            return (returnParam.Value != null)
                        ? Convert.ToInt32(returnParam.Value)
                        : 0;
        }

        #endregion
    }
}
